Full Stack Web Development Internship Program
- 29k Enrolled Learners
- Weekend/Weekday
- Live Class
Java, being one of the most prominent programming languages, provides extensive support to databases. It helps us to connect to various databases through JDBC (Java Database Connectivity). In this article, I will tell you how to connect to a database and execute queries using JDBC.
Below topics are covered in this article:
JDBC is one of the standard Java API for database-independent connectivity between the Java programming language and a wide range of databases. This API lets you encode the access request statements, in Structured Query Language (SQL). This mainly involves opening a connection, creating a SQL Database, executing SQL queries and then arriving at the output.
JDBC API can be used to access tabular data stored in any relational database. With this, you can update, save, fetch and delete the data from the databases. It is similar to the Open Database Connectivity (ODBC) provided by Microsoft.
For a better understanding of working of JDBC, let’s dive deeper into the topic and understand the architecture that lies behind Java Database Connectivity.
The JDBC API provides the following interfaces and classes −
DriverManager: This is mainly used to manage a list of database drivers. The driver that recognizes a certain sub-protocol will be used to establish a database Connection.
The Driver is an interface that handles the communications with the database server. It also abstracts the details that are associated while working with Driver objects.
A Connection is an interface that consists of all the methods required to connect to a database. The connection object deals with the communication functions of the database. context.
Now let’s move on to the next topic and look at the steps required to create a JDBC Application.
In order to create a JDBC Application, you need to follow a few steps. Let’s see what are they.
Import the packages: You need to include all the packages that contain the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
Register the JDBC driver: Here you have to initialize a driver so that you can open a communication channel with the database.
Open a connection: Here, you can use the getConnection() method to create a Connection object, which represents a physical connection with the database.
Execute a query: This actually requires to use an object of type Statement for building and submitting an SQL statement to the database.
Extract data from the result set: It is suggested that you use the appropriate getXXX() method to retrieve the data from the result set.
Clean up the environment: Here, it is essential to explicitly close all database resources versus relying on the JVM’s garbage collection.
Now as you have seen various steps involved to create a JDBC Application, let’s see an example code to create a database and establish a connection.
package Edureka; import java.sql.*; import java.sql.DriverManager; public class Example { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/emp"; // Database credentials static final String USER = "root"; static final String PASS = ""; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ //STEP 2: Register JDBC driver Class.forName("com.mysql.cj.jdbc.Driver"); //STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,"root",""); //STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql; sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); //STEP 5: Extract data from result set while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } //STEP 6: Clean-up environment rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); }finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close() }catch(SQLException se2){ }// nothing can be done try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try }//end try System.out.println("Goodbye!"); }//end main } // end Example
Above code creates a table in your localhost database. To insert the values in the created database, you can refer to the below code. I will be writing the code only for step 4. Rest of the code remains the same as above.
//STEP 4: Execute a query System.out.println("Creating table in given database..."); stmt = conn.createStatement(); String sql = "CREATE TABLE EMPLOYEES " + "(id INTEGER not NULL, " + " first VARCHAR(255), " + " last VARCHAR(255), " + " age INTEGER, " + " PRIMARY KEY ( id ))"; stmt.executeUpdate(sql); System.out.println("Created table in given database..."); System.out.println("Inserting records into the table..."); stmt =conn.createStatement(); String sql ="INSERT INTO Employees VALUES (100, 'Kriss', 'Kurian', 18)"; stmt.executeUpdate(sql); sql = "INSERT INTO Employees VALUES (101, 'Enrique', 'John', 25)"; stmt.executeUpdate(sql); sql= "INSERT INTO Employees (102, 'Taylor', 'Swift', 30)"; stmt.executeUpdate(sql); sql= "INSERT INTO Employees VALUES(103, 'Linkin', 'Park', 28)"; stmt.executeUpdate(sql); System.out.println("Inserted records into the table...");
So this is how you can establish a connection to the database and insert values in the tables. Now let’s move further and understand various JDBC Driver Types
JDBC drivers are used to implement the defined interfaces in the JDBC API, for interacting with the database server. Essentially, a JDBC driver does three things and they are as follows:
1. Establishes a connection with a data source.
2. It will send queries and update statements to the data source.
3. Finally, it processes the results.
For example, JDBC drivers help you to open a database connection to interact with it by sending SQL or database commands. If you wish to know more about types of JDBC Driver, you can refer to this article on JDBC Drivers.
Now let’s move further and understand JDBC Connections.
Import JDBC Packages: Add import statements to your Java program to import required classes in your Java code.
Register JDBC Driver: In this step, JVM to loads the desired driver implementation into memory so that it can fulfill the JDBC requests. There are 2 approaches to register a driver.
The most suitable approach to register a driver is to use Java’s forName() method to dynamically load the driver’s class file into memory, which automatically registers it. This method is suitable as it allows you to make the driver registration configurable and portable. Take a look at the below code:
try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch(ClassNotFoundException ex) System.out.println("Error: unable to load driver class!"); System.exit(1); }
The second approach you can use to register a driver is to use the static registerDriver()method.
try { Driver myDriver = new oracle.jdbc.driver.OracleDriver(); DriverManager.registerDriver( myDriver ); } catch(ClassNotFoundException ex) { System.out.println("Error: unable to load driver class!"); System.exit(1); }
You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by Microsoft. Here each form requires a database URL.
Database URL Formulation: URL Formulation is necessary to create a properly formatted address that points to the database to which you want to connect. Once you loaded the driver, you can establish a connection using the DriverManager.getConnection() method. DriverManager.getConnection() methods are−
getConnection(String url)
getConnection(String url, Properties prop)
getConnection(String url, String user, String password)
You can create a connection using the database URL, username, and password and also using properties object.
Finally, to end the database session, you need to close all the database connections. However, if you forget, Java’s garbage collector will close the connection when it cleans up stale objects.
conn.close(); // Used to close the connection
That was all about Java Database Connectivity. If you wish to know more about JDBC, you can refer to this article on Advanced Java Tutorial. This brings us to the end of the article on ‘how to connect to a database’. I hope I have thrown some light on to your knowledge on JDBC.
Check out the Java Online Training by Edureka, a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe. We are here to help you with every step on your journey, for becoming a besides this java interview questions, we come up with a curriculum which is designed for students and professionals who want to be a Java Developer.
Got a question for us? Please mention it in the comments section of this “how to connect to a database” article and we will get back to you as soon as possible.
Course Name | Date | Details |
---|---|---|
Java Course Online | Class Starts on 7th December,2024 7th December SAT&SUN (Weekend Batch) | View Details |
edureka.co
Nice Article that can be explainable to a beginners also